package com.mbc.rmt.dao;

import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;

import com.mbc.rmt.data.BankDataList;
import com.mbc.rmt.entities.Bank;
import com.mbc.rmt.entities.BankRpt;
import com.mbc.common.dao.DAO;

public class BankDAOImpls extends DAO<Bank> implements BankDAO {

	public BankDAOImpls(Connection conn)
	{
		this.conn = conn;
		this.table_name = "Bank";
	}

	@Override
	public void create(Bank entity) throws SQLException {
		query = "insert into " + table_name + " ( BankId, BankCode, Description,COAKey,departmentID, BranchId) values( ?, ?, ?, ?, ?, ?)";
		pstmt = conn.prepareStatement(query);
		int i = 1;
				pstmt.setLong(i++, entity.getBankId());
		pstmt.setString(i++, entity.getBankCode());
		pstmt.setString(i++, entity.getDescription());
		pstmt.setLong(i++, entity.getCoaid());
		pstmt.setLong(i++, entity.getDepartmentid());
		pstmt.setLong(i++, entity.getBranchId());
		pstmt.executeUpdate();
		pstmt.close();
	}

	@Override
	public void update(Bank entity) throws SQLException {
		query = "update " + table_name + " set Description = ?, COAKey = ?, departmentID = ?, BranchId = ?" + 
				" where BankId = " + entity.getBankId();
		pstmt = conn.prepareStatement(query);
		int i = 1;
		pstmt.setString(i++, entity.getDescription());
		pstmt.setLong(i++, entity.getCoaid());
		pstmt.setLong(i++, entity.getDepartmentid());
		pstmt.setLong(i++, entity.getBranchId());
		pstmt.executeUpdate();
		pstmt.close();
	}

	@Override
	public void delete(long key) throws SQLException {
		query = getDeleteQuery() + " where BankId = " + key;
		pstmt = conn.prepareStatement(query);
		pstmt.executeUpdate();
		pstmt.close();
	}

	@Override
	public Bank find(long key) throws SQLException {
		Bank l_Bank = null;
		query = getSelectQuery() + " where BankId = " + key;
		pstmt = conn.prepareStatement(query);
		rs = pstmt.executeQuery();
		if(rs.next())
			l_Bank = readRecords(rs);
		rs.close();
		pstmt.close();
		return l_Bank;
	}

	@Override
	public ArrayList<Bank> findAll() throws SQLException {
		ArrayList<Bank> l_BankList = new ArrayList<Bank>();
		query = getSelectQuery();
		pstmt = conn.prepareStatement(query);
		rs = pstmt.executeQuery();
		while (rs.next()) {
			l_BankList.add(readRecord(rs));
		}
		rs.close();
		pstmt.close();
		return l_BankList;
	}

	@Override
	protected Bank readRecord(ResultSet rs) throws SQLException {
		Bank l_entity = new Bank();
		l_entity.setBankId(rs.getLong("BankId"));
		l_entity.setBankCode(rs.getString("BankCode"));
		l_entity.setDescription(rs.getString("Description"));
		l_entity.setCoaid(rs.getLong("COAKey"));
		l_entity.setCoacode(rs.getString("AC_ID"));
		l_entity.setDepartmentid(rs.getLong("departmentID"));
		l_entity.setDepartmentcode(rs.getString("departmentCode"));
		l_entity.setBranchId(rs.getLong("BranchId"));
		return l_entity;
	}
	
	protected Bank readRecords(ResultSet rs) throws SQLException {
		Bank l_entity = new Bank();
		l_entity.setBankId(rs.getLong("BankId"));
		l_entity.setBankCode(rs.getString("BankCode"));
		l_entity.setDescription(rs.getString("Description"));
		l_entity.setCoaid(rs.getLong("COAKey"));
		l_entity.setDepartmentid(rs.getLong("departmentID"));
		l_entity.setBranchId(rs.getLong("BranchId"));
		return l_entity;
	}

	@Override
	public BankDataList findBanks(Bank cri) throws SQLException {
		BankDataList l_DataList = new BankDataList();
		ArrayList<Bank> l_entityList = new ArrayList<Bank>();
		/*String l_cri = " and (BankCode like N'%" + cri.getKeyword() + "%'" +
				" or b.Description like N'%" + cri.getKeyword() + "%'" +
				" or b.COAKey like N'%" + cri.getKeyword() + "%'" +
				" or b.departmentID like N'%" + cri.getKeyword() +"%')" +
				" and b.BranchId = " + cri.getBranchId();*/
		String l_cri = " and (" + cri.getFieldname() +" like N'%" + cri.getKeyword() + "%') and b.BranchId = " + cri.getBranchId();
		
		query = "set rowcount " + cri.getSize() +
			" select * from (select row_number() over(order by BankId desc) as [rowno], b.BankId, " +
			" b.BankCode, b.Description,b.COAKey, coa.AC_ID,b.departmentID, d.departmentCode, b.BranchId  " +
			" from Bank b " +
			" left join COA coa on coa.COAKey = b.COAKey " +
			" left join Department d on d.departmentID = b.departmentID " +
			" where 1 = 1" + l_cri + ") " + table_name +
			" where rowno > " + cri.getIndex();
		pstmt = conn.prepareStatement(query);
		rs = pstmt.executeQuery();
		while (rs.next()) {	
			l_entityList.add(readRecord(rs));
		} 
		l_DataList.setEntityList(l_entityList);
		rs.close();
		pstmt.close();
		String l_cri1 = " and (BankCode like N'%" + cri.getKeyword() + "%'" +
				" or Description like N'%" + cri.getKeyword() + "%'" +
				" or COAKey like N'%" + cri.getKeyword() + "%'" +
				" or departmentID like N'%" + cri.getKeyword() +"%')" +
				" and BranchId = " + cri.getBranchId();
		query = "select count(BankId) from " + table_name + " where 1 = 1" + l_cri1;
		pstmt = conn.prepareStatement(query);
		rs = pstmt.executeQuery();
		if(rs.next()){
			l_DataList.setTotalRecord(rs.getLong(1));
		}
		rs.close();
		pstmt.close();
		return l_DataList;
	}

	@Override
	public Bank findByCode(String code, long branchId) throws SQLException {
		Bank l_Bank = null;
		query = "select row_number() over(order by b.BankId desc)as [rowno], " +
				" b.BankId,b.BankCode,b.Description, " +
				" b.BranchId, c.COAKey,c.AC_ID,d.departmentID,d.departmentCode " +
				" from Bank b " +
				" left join COA c on c.COAKey = b.COAKey " +
				" left join Department d on d.departmentID = b.departmentID" + 
				" where BankCode = '" + code + "' and b.branchId = " + branchId;
		pstmt = conn.prepareStatement(query);
		rs = pstmt.executeQuery();
		if(rs.next())
			l_Bank = readRecord(rs);
		rs.close();
		pstmt.close();
		return l_Bank;
	}
	@Override
	public ResultSet prepareBankReport(BankRpt entity)
			throws SQLException {
		ResultSet l_rs;
		String l_query = "dbo.sp_cr_bankrpt ?, ?, ?";

		CallableStatement l_pstmt = conn.prepareCall(l_query);
		BankRptParameter(l_pstmt, entity);
		l_rs = l_pstmt.executeQuery();
		return l_rs;

	}
	private void BankRptParameter(CallableStatement l_pstmt,
			BankRpt entity) throws SQLException {
		int i = 1;

		l_pstmt.setString(i++, entity.getFromBankID());
		l_pstmt.setString(i++, entity.getToBankID());
		l_pstmt.setLong(i++, entity.getBranchID());
	}
	/*public BankDataList getBankList(BankRpt entity) throws SQLException {
		BankDataList l_DataList = new BankDataList();
		ArrayList<Bank> l_BankList = new ArrayList<Bank>();
		String l_query = "dbo.sp_cr_bankrpt ?, ?, ?";
		CallableStatement l_pstmt = conn.prepareCall(l_query);
		BankRptParameter(l_pstmt, entity);
		ResultSet l_rs = l_pstmt.executeQuery();
		while (l_rs.next()) {
			l_BankList.add(readRecordFT(l_rs));
		}
		l_DataList.setEntityList(l_BankList);
		l_rs.close();
		l_pstmt.close();
		return l_DataList;
	}
	private static Bank readRecordFT(ResultSet rs) throws SQLException {
		Bank l_entity = new Bank();
		l_entity.setBankCode(rs.getString("BankCode"));
		l_entity.setDescription(rs.getString("description"));
		return l_entity;
	}*/

}
